package com.hrhx.duhongming.web;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.Arrays;
import java.util.List;

import org.jxls.common.Context;
import org.jxls.demo.EachIfCommandDemo;
import org.jxls.demo.guide.Employee;
import org.jxls.demo.guide.ObjectCollectionDemo;
import org.jxls.demo.model.Department;
import org.jxls.jdbc.JdbcHelper;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.hrhx.duhongming.config.ConstantExcelPath;
import com.hrhx.duhongming.util.preview.ToHtml;

@Controller
@RequestMapping(value = "/jxls2")
public class Jxls2Controller {
	
	@RequestMapping(value = "{templates}/{suffix}", method = RequestMethod.GET)
	public String templates(@PathVariable("templates") String templates,
							@PathVariable("suffix") String suffix,
							ModelMap modelMap) {
		String templateFileName = ConstantExcelPath.getExcelTemplatePath()+templates+"."+suffix;
		String destFileName = ConstantExcelPath.getExcelTempPath()+templates+"."+suffix;
		Context context = null;
		String[] objectCollectionArr = new String[]{"object_collection_template",
				"object_collection_column_template",
				"formulas_template",
				"grouping_template",
				"param_formulas_template"};
		String[] multisheetMarkupArr = new String[]{"multisheet_markup_demo","multisheet_markup_demo-2"};
				
		try(InputStream is = new FileInputStream(templateFileName)) {
			try (OutputStream os = new FileOutputStream(destFileName)) {
				if(Arrays.asList(objectCollectionArr).contains(templates)){
					context = generateSampleEmployeeData();
					JxlsHelper.getInstance().processTemplate(is, os, context);
				}else if(Arrays.asList(multisheetMarkupArr).contains(templates)){
					context = createDepartments();
					JxlsHelper.getInstance().processTemplate(is, os, context);
				}else if("grid_template".equalsIgnoreCase(templates)){
					context = generateSampleEmployeeData();
					context.putVar("headers", Arrays.asList("Name", "Birthday", "Payment"));
					JxlsHelper.getInstance().processGridTemplateAtCell(is, os, context, "name,birthDate,payment","Sheet1!A1");
				}else if("sql_demo_template".equalsIgnoreCase(templates)){
					context = getSqlData();
					JxlsHelper.getInstance().processTemplate(is, os, context);
				}	
				ToHtml toHtml = ToHtml.create(destFileName,new StringBuilder());
				toHtml.setCompleteHTML(true);
				modelMap.put("excelHtml",toHtml.printPage());
				modelMap.put("templates",templates);
				modelMap.put("suffix",suffix);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "/jxls2/demo";
	}
	
	private Context createDepartments(){
		 List<Department> departments = EachIfCommandDemo.createDepartments();
		 Context context = PoiTransformer.createInitialContext();
         context.putVar("departments", departments);
         context.putVar("sheetNames", Arrays.asList(
                 departments.get(0).getName(),
                 departments.get(1).getName(),
                 departments.get(2).getName()));
         return context;
	}
	
	private Context generateSampleEmployeeData(){
		Context context = new Context();
		List<Employee> employees;
		try {
			employees = ObjectCollectionDemo.generateSampleEmployeeData();
			context.putVar("employees", employees);
			context.putVar("data", employees);
			context.putVar("bonus", 0.1);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        return context;
    }
	
	public static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
    public static final String CONNECTION_URL = "jdbc:derby:memory:testDB;create=true";

    private Context getSqlData() throws Exception {
    	Context context = new Context();
	   	 Class.forName(DRIVER);
	     try (Connection conn = DriverManager.getConnection(CONNECTION_URL)){
	         initData(conn);
	         JdbcHelper jdbcHelper = new JdbcHelper(conn);
             context.putVar("conn", conn);
             context.putVar("jdbc", jdbcHelper);
	     }
	     return context;
	}

    private static void initData(Connection conn) throws SQLException, ParseException {
        String createTableSlq = "CREATE TABLE employee (" +
                "id INT NOT NULL, " +
                "name VARCHAR(20) NOT NULL, " +
                "birthdate DATE, " +
                "payment DECIMAL, " +
                "PRIMARY KEY (id))";
        String insertSql = "INSERT INTO employee VALUES (?,?,?,?)";
        List<Employee> employees = ObjectCollectionDemo.generateSampleEmployeeData();
            try(Statement stmt = conn.createStatement()){
                stmt.executeUpdate(createTableSlq);
                int k = 1;
                try(PreparedStatement insertStmt = conn.prepareStatement(insertSql)){
                    for (Employee employee : employees) {
                        insertStmt.setInt(1, k++);
                        insertStmt.setString(2, employee.getName());
                        insertStmt.setDate(3, new Date(employee.getBirthDate().getTime()));
                        insertStmt.setBigDecimal(4, employee.getPayment());
                        insertStmt.executeUpdate();
                    }
                }
        }
       
    }
}
